/*=========================================================================
create_tts_broker_package.pls - Created by Dave Herrington, DARC Corporation


Copyright (c) 2009, Dave Herrington, DARC Corporation and Proofspace, Inc.
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

- Redistributions of source code must retain the above copyright notice,
  this list of conditions and the following disclaimer.

- Redistributions in binary form must reproduce the above copyright notice,
  this list of conditions and the following disclaimer in the documentation
  and/or other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE
LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR
CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF
SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS
INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN
CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE
POSSIBILITY OF SUCH DAMAGE.


Comments:

This package is contains the package specification for trusted timestamping
procedures.  This package should not be executable outside of the TTS_ADMIN
schema.  Run this script connected to the TTS_ADMIN schema.


Usage:

sqlplus TTS_ADMIN/<TTS_ADMIN Password> @create_tts_broker_package.pls


History:

08/06/09 D.Herrington - Created. 
08/18/09 D.Herrington - Added Changed_By handling.

===========================================================================*/

CREATE OR REPLACE PACKAGE tts_admin.tts_broker AS

FUNCTION hash_md5 (
    in_message IN VARCHAR2
)
RETURN VARCHAR2 DETERMINISTIC;

FUNCTION hash_sha128 (
    in_message IN VARCHAR2
)
RETURN VARCHAR2 DETERMINISTIC;

FUNCTION hash_sha256 (
    in_message IN VARCHAR2
)
RETURN VARCHAR2 DETERMINISTIC;

FUNCTION hash_sha512 (
    in_message IN VARCHAR2
)
RETURN VARCHAR2 DETERMINISTIC;

PROCEDURE log_change (
	p_change_type	IN	VARCHAR2,
	p_changed_by	IN	VARCHAR2,
	p_table_id	IN	NUMBER,
	p_record_id	IN	VARCHAR2,
	p_col01_old	IN	VARCHAR2,
	p_col01_new	IN	VARCHAR2,
	p_col02_old	IN	VARCHAR2,
	p_col02_new	IN	VARCHAR2,
	p_col03_old	IN	VARCHAR2,
	p_col03_new	IN	VARCHAR2,
	p_col04_old	IN	VARCHAR2,
	p_col04_new	IN	VARCHAR2,
	p_col05_old	IN	VARCHAR2,
	p_col05_new	IN	VARCHAR2,
	p_col06_old	IN	VARCHAR2,
	p_col06_new	IN	VARCHAR2,
	p_col07_old	IN	VARCHAR2,
	p_col07_new	IN	VARCHAR2,
	p_col08_old	IN	VARCHAR2,
	p_col08_new	IN	VARCHAR2,
	p_col09_old	IN	VARCHAR2,
	p_col09_new	IN	VARCHAR2,
	p_col10_old	IN	VARCHAR2,
	p_col10_new	IN	VARCHAR2,
	p_col11_old	IN	VARCHAR2,
	p_col11_new	IN	VARCHAR2,
	p_col12_old	IN	VARCHAR2,
	p_col12_new	IN	VARCHAR2,
	p_col13_old	IN	VARCHAR2,
	p_col13_new	IN	VARCHAR2,
	p_col14_old	IN	VARCHAR2,
	p_col14_new	IN	VARCHAR2,
	p_col15_old	IN	VARCHAR2,
	p_col15_new	IN	VARCHAR2);

PROCEDURE validate_changes (
	p_min_change_id	IN	NUMBER	DEFAULT 0,
        p_max_change_id IN	NUMBER	DEFAULT 99999999999999999999,
        p_min_change_date IN	DATE	DEFAULT '01-JAN-0001',
        p_max_change_date IN	DATE	DEFAULT '31-DEC-9999');

END tts_broker;
/
